<?php

/**
 * The Job Model
 *
 * @author Highstrike (highstrike@gmail.com)
 * @package CargoClaims
 * @version 1.0.dev
 */
class App_Model_Job extends MVC_Model
{
	/**
	 * This is a model that a database, so set up to use it
	 *
	 * @param MVC_Database $database
	 */
	public function __construct(MVC_Database &$database)
	{
		$this-> use_Database($database, 'db');
	}

	public function get_Categories()
	{
		$q = 'SELECT l.sid, l.value, COUNT(j.sid) AS jobs
			FROM listing_field_list AS l
			LEFT JOIN listings AS j ON FIND_IN_SET(l.sid, j.JobCategory)

			WHERE l.field_sid = 198 AND j.sid IS NOT NULL AND j.EmploymentType IS NOT NULL AND j.complete = 1 AND j.expiration_date > NOW()
			GROUP BY l.sid
			ORDER BY l.order ASC';

		return $this-> db-> query($q);
	}

	public function get_Featured_Companies()
	{
		$q = 'SELECT u.sid, u.CompanyName as name, f.saved_file_name as file
			FROM users AS u
			LEFT JOIN uploaded_files AS f ON f.id = CONCAT(u.Logo, "_thumb")

			WHERE f.saved_file_name IS NOT NULL AND u.featured = 1 AND u.active = 1 AND u.approval = "Approved" AND u.user_group_sid = "41"
			ORDER BY u.sid ASC';

		return $this-> db-> query($q);
	}

	public function get_Featured_Jobs()
	{
		$q = 'SELECT j.sid, j.title, j.user_sid, u.CompanyName, j.activation_date AS date, j.JobCategory AS cids, group_concat(DISTINCT l.value SEPARATOR "{,}") AS categories, group_concat(DISTINCT l2.value SEPARATOR "{,}") AS jobtypes
			FROM listings AS j
			LEFT JOIN listing_field_list AS l ON FIND_IN_SET(l.sid, j.JobCategory)
			LEFT JOIN listing_field_list AS l2 ON FIND_IN_SET(l2.sid, j.EmploymentType)
			LEFT JOIN users AS u ON j.user_sid = u.sid

			WHERE j.featured = "1" AND j.active = "1" AND j.EmploymentType IS NOT NULL AND j.complete = 1 AND j.expiration_date > NOW()
			GROUP BY j.sid
			ORDER BY j.activation_date DESC
		';

		return $this-> db-> query($q);
	}

	public function get_Latest_Jobs()
	{
		$q = 'SELECT j.sid, j.title, j.user_sid, u.CompanyName, j.activation_date AS date, j.JobCategory AS cids, group_concat(DISTINCT l.value SEPARATOR "{,}") AS categories, group_concat(DISTINCT l2.value SEPARATOR "{,}") AS jobtypes
			FROM listings AS j
			LEFT JOIN listing_field_list AS l ON FIND_IN_SET(l.sid, j.JobCategory)
			LEFT JOIN listing_field_list AS l2 ON FIND_IN_SET(l2.sid, j.EmploymentType)
			LEFT JOIN users AS u ON j.user_sid = u.sid

			WHERE j.active = "1" AND j.EmploymentType IS NOT NULL AND j.complete = 1 AND j.expiration_date > NOW()
			GROUP BY j.sid
			ORDER BY j.activation_date DESC
			LIMIT 5
		';

		return $this-> db-> query($q);
	}

	public function get_Jobs_In_Category($id, $page = 1, $limit = 15)
	{
		$q = 'SELECT SQL_CALC_FOUND_ROWS j.sid, j.title, j.user_sid, u.CompanyName, j.activation_date AS date, j.JobCategory AS cids, group_concat(DISTINCT l.value SEPARATOR "{,}") AS categories, group_concat(DISTINCT l2.value SEPARATOR "{,}") AS jobtypes
			FROM listings AS j
			LEFT JOIN listing_field_list AS l ON FIND_IN_SET(l.sid, j.JobCategory)
			LEFT JOIN listing_field_list AS l2 ON FIND_IN_SET(l2.sid, j.EmploymentType)
			LEFT JOIN users AS u ON j.user_sid = u.sid

			WHERE FIND_IN_SET("'. $this-> db-> real_escape_string($id) .'", j.JobCategory) AND j.active = "1" AND j.EmploymentType IS NOT NULL AND j.complete = 1 AND j.expiration_date > NOW()
			GROUP BY j.sid
			ORDER BY j.activation_date DESC
			LIMIT '. $this-> db-> real_escape_string(($page - 1) * $limit) .', '. $this-> db-> real_escape_string($limit);

		return $this-> db-> query($q);
	}

	public function get_Found_Rows()
	{
		$q = 'SELECT FOUND_ROWS() AS total_rows';
		$r = $this-> db-> query($q)->current();

		return $r['total_rows'];
	}
}